﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using Excel;
using OfficeOpenXml;
using UnityEditor;
using UnityEngine;

namespace Localize
{
    public class i18nExcelExpoter
    {
        public class LanguageDict
        {
            public Dictionary<string, string> book;

            public LanguageDict() : base()
            {
                book = new Dictionary<string, string>();
            }

            public string AddKeyValue(string key, string val, int fix)
            {
                if (book.ContainsKey(key))
                    key = key + fix;
                book.Add(key, val);
                return key;
            }
        }

        public static string Excel
        {
            get { return Application.dataPath + "/../Excel.xlsx"; }
        }

        static string sheetName
        {
            get { return "CN"; }
        }

        [MenuItem("Tools/i18n/导出Excel(多Sheet)")]
        public static void exportExcel()
        {
            Dictionary<ELanguage, LanguageDict> langs = new Dictionary<ELanguage, LanguageDict>(5);
            List<string> allkeys = new List<string>(100);
            foreach (ELanguage eLang in Enum.GetValues(typeof(ELanguage)))
            {
                if (eLang != ELanguage.None)
                {
                    i18n.Reset();
                    i18n.Language = eLang;
                    LanguageDict ld = new LanguageDict();
                    int i = 0;
                    foreach (KeyValuePair<string, string> kv in i18n.langContentDic)
                    {
                        string key = kv.Key;
                        string val = kv.Value;
                        key = ld.AddKeyValue(key, val, i);
                        if (!allkeys.Contains(key))
                            allkeys.Add(key);
                        i++;
                    }

                    langs.Add(eLang, ld);
                }
            }

            WriteExcel(Excel, allkeys, langs);
            Debug.Log("已生成Excel翻译文件");
        }

        [MenuItem("Tools/i18n/Excel导入(多Sheet)")]
        public static void ImportExcel()
        {
            //查询excel表中数据，赋值给asset文件
            Dictionary<string, LanguageDict> langs = LoadLangs();
            foreach (var dict in langs)
            {
                string language = dict.Key;
                StringBuilder property = new StringBuilder(1000);
                foreach (var lkv in dict.Value.book)
                {
                    string key = lkv.Key;
                    string val = i18n.NewLine2RN(lkv.Value);
                    property.AppendLine(key + "=" + val);
                }

                string file = R.RawResourcesRoot + "Localized/" + language + "/properties.txt";
                File.WriteAllText(file, property.ToString());
            }

            AssetDatabase.Refresh();
            Debug.Log("Import Success!");
        }

        //查询menu表
        public static Dictionary<string, LanguageDict> LoadLangs()
        {
            if (!File.Exists(Excel))
                throw new FileNotFoundException(Excel);
            Dictionary<string, LanguageDict> allLangs = new Dictionary<string, LanguageDict>(5);
            FileStream fs = new FileStream(new FileInfo(Excel).FullName, FileMode.Open, FileAccess.Read,
                FileShare.Read);
            using (ExcelPackage package = new ExcelPackage(fs))
            {
                //在excel空文件添加新sheet
                if (package.Workbook.Worksheets.Count != 0)
                {
                    ExcelWorksheet worksheet = package.Workbook.Worksheets[sheetName];
                    string langName = worksheet.Name;
                    allLangs.Add(langName, new LanguageDict());
                    for (int r = worksheet.Dimension.Start.Row; r <= worksheet.Dimension.End.Row; r++)
                    {
                        string key = "key_" + r;
                        key = worksheet.Cells[r, 1].Value.ToString();
                        object translate = worksheet.Cells[r, 2].Value;
                        if (translate != null)
                        {
                            string text = translate.ToString();
                            if (!string.IsNullOrWhiteSpace(text))
                                allLangs[langName].AddKeyValue(key, text, r);
                        }
                    }
                }
            }

            fs.Close();
            return allLangs;
        }

        /// <summary>
        /// 读取 Excel ; 需要添加 Excel.dll; System.Data.dll;
        /// </summary>
        /// <param name="excelName">excel文件名</param>
        /// <param name="sheetName">sheet名称</param>
        /// <returns>DataRow的集合</returns>
        static DataRowCollection ReadExcel(string path, string sheetName)
        {
            FileStream stream = File.Open(path, FileMode.Open, FileAccess.Read, FileShare.Read);
            IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

            DataSet result = excelReader.AsDataSet();
            //int columns = result.Tables[0].Columns.Count;
            //int rows = result.Tables[0].Rows.Count;

            //tables可以按照sheet名获取，也可以按照sheet索引获取
            //return result.Tables[0].Rows;
            if (result.Tables.Contains(sheetName))
                return result.Tables[sheetName].Rows;
            return result.Tables[0].Rows;
        }

        /// <summary>
        /// 写入 Excel ; 需要添加 OfficeOpenXml.dll;
        /// </summary>
        /// <param name="excelName">excel文件名</param>
        /// <param name="sheetName">sheet名称</param>
        public static void WriteExcel(string path, List<string> keys, Dictionary<ELanguage, LanguageDict> landict)
        {
            if (!File.Exists(path))
                File.Create(path);
            using (ExcelPackage package = new ExcelPackage()) // new ExcelPackage(fs))
            {
                //在excel空文件添加新sheet
                ExcelWorksheet worksheet = package.Workbook.Worksheets[sheetName];
                //添加列名
                List<KeyValuePair<ELanguage, LanguageDict>> landictToList = landict.ToList();
                landictToList.Sort((l, r) =>
                {
                    if (l.Key == ELanguage.CN)
                        return -1;
                    if (r.Key == ELanguage.CN)
                        return 1;
                    return l.Key - r.Key;
                });
                //一个语言一个sheet
                foreach (var langDic in landictToList)
                {
                    ELanguage lang = langDic.Key;
                    LanguageDict translation = landict[lang];
                    worksheet = package.Workbook.Worksheets[lang.ToString()];
                    if (worksheet == null)
                        worksheet = package.Workbook.Worksheets.Add(lang.ToString());
                    // key / translate
                    int row = 1;
                    foreach (string key in keys)
                    {
                        worksheet.Cells[row, 1].Value = key;
                        if (translation.book.ContainsKey(key))
                            worksheet.Cells[row, 2].Value = translation.book[key];
                        else
                            worksheet.Cells[row, 2].Value = null;
                        row += 1;
                    }

                    worksheet.Column(1).Width = 20;
                    worksheet.Column(2).Width = 100;
                }

                //保存excel
                using (FileStream fs = new FileStream(Excel, FileMode.OpenOrCreate, FileAccess.ReadWrite,
                    FileShare.ReadWrite))
                {
                    package.SaveAs(fs);
                }
            }
        }


        [MenuItem("Tools/i18n/导出Excel(单Sheet)")]
        public static void exportExcelSingleSheet()
        {
            Dictionary<ELanguage, LanguageDict> langs = new Dictionary<ELanguage, LanguageDict>(5);

            List<string> allkeys = new List<string>(100);
            foreach (ELanguage eLang in Enum.GetValues(typeof(ELanguage)))
            {
                if (eLang != ELanguage.None)
                {
                    i18n.Reset();
                    i18n.Language = eLang;
                    LanguageDict ld = new LanguageDict();
                    int i = 0;
                    foreach (KeyValuePair<string, string> kv in i18n.langContentDic)
                    {
                        string key = kv.Key;
                        string val = kv.Value;
                        key = ld.AddKeyValue(key, val, i);
                        if (!allkeys.Contains(key))
                            allkeys.Add(key);
                        i++;
                    }

                    langs.Add(eLang, ld);
                }
            }

            WriteExcelSingleSheet(Excel, allkeys, langs);
            Debug.Log("已生成Excel翻译文件");
        }

        [MenuItem("Tools/i18n/Excel导入(单Sheet)")]
        public static void ImportExcelSingleSheet()
        {
            //查询excel表中数据，赋值给asset文件
            Dictionary<ELanguage, LanguageDict> langs = LoadLangsSingleSheet();
            foreach (var dict in langs)
            {
                ELanguage language = dict.Key;
                if (language == ELanguage.None) continue;
                i18n.Reset();
                i18n.Language = language;
                StringBuilder property = new StringBuilder(1000);
                foreach (var lkv in dict.Value.book)
                {
                    string key = lkv.Key;
                    string val = i18n.NewLine2RN(lkv.Value);
                    property.AppendLine(key + "=" + val);
                }

                string file = R.RawResourcesRoot + "Localized/" + language.ToString() + "/properties.txt";
                File.WriteAllText(file, property.ToString());
            }

            AssetDatabase.Refresh();
            Debug.Log("Import Success!");
        }

        //查询menu表
        public static Dictionary<ELanguage, LanguageDict> LoadLangsSingleSheet()
        {
            if (!File.Exists(Excel))
                throw new FileNotFoundException(Excel);
            Dictionary<int, ELanguage> langIndx = new Dictionary<int, ELanguage>(5);
            Dictionary<ELanguage, LanguageDict> allLangs = new Dictionary<ELanguage, LanguageDict>(5);
            FileStream fs = new FileStream(new FileInfo(Excel).FullName, FileMode.Open, FileAccess.Read,
                FileShare.Read);
            using (ExcelPackage package = new ExcelPackage(fs))
            {
                //在excel空文件添加新sheet
                if (package.Workbook.Worksheets.Count != 0)
                {
                    ExcelWorksheet worksheet = package.Workbook.Worksheets[sheetName];
                    for (int r = worksheet.Dimension.Start.Row; r <= worksheet.Dimension.End.Row; r++)
                    {
                        if (r == 1) //第一行是语种
                        {
                            for (int c = worksheet.Dimension.Start.Column; c <= worksheet.Dimension.End.Column; c++)
                            {
                                if (c != 1 && worksheet.Cells[r, c].Value != null) //第一列是 key
                                {
                                    ELanguage el;
                                    if (Enum.TryParse<ELanguage>(worksheet.Cells[r, c].Value.ToString(), out el))
                                    {
                                        if (allLangs.ContainsKey(el))
                                            continue;
                                        langIndx[c] = el;
                                        allLangs.Add(el, new LanguageDict());
                                    }
                                }
                            }
                        }
                        else
                        {
                            string key = "key_" + r;
                            for (int c = worksheet.Dimension.Start.Column; c <= worksheet.Dimension.End.Column; c++)
                            {
                                if (c == 1)
                                    key = worksheet.Cells[r, c].Value.ToString();
                                else
                                {
                                    if (langIndx.ContainsKey(c))
                                    {
                                        ELanguage language = langIndx[c];
                                        LanguageDict languageDict;
                                        if (allLangs.TryGetValue(language, out languageDict))
                                        {
                                            if (worksheet.Cells[r, c].Value != null)
                                            {
                                                string text = worksheet.Cells[r, c].Value.ToString();
                                                if (!string.IsNullOrWhiteSpace(text))
                                                    languageDict.AddKeyValue(key, text, r);
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }

            fs.Close();
            return allLangs;
        }

        /// <summary>
        /// 写入 Excel ; 需要添加 OfficeOpenXml.dll;
        /// </summary>
        /// <param name="excelName">excel文件名</param>
        /// <param name="sheetName">sheet名称</param>
        public static void WriteExcelSingleSheet(string path, List<string> keys,
                                                 Dictionary<ELanguage, LanguageDict> landict)
        {
            if (!File.Exists(path))
                File.Create(path);
            using (ExcelPackage package = new ExcelPackage()) // new ExcelPackage(fs))
            {
                //在excel空文件添加新sheet
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(sheetName);
                //添加列名
                List<KeyValuePair<ELanguage, LanguageDict>> landictToList = landict.ToList();
                landictToList.Sort((l, r) =>
                {
                    if (l.Key == ELanguage.CN)
                        return -1;
                    if (r.Key == ELanguage.CN)
                        return 1;
                    return l.Key - r.Key;
                });
                int row = 1;
                foreach (string key in keys)
                {
                    int colum = 1;
                    if (row == 1)
                    {
                        worksheet.Cells[1, 1].Value = "Key/Lang";
                        worksheet.Column(colum).Width = 20;
                        foreach (KeyValuePair<ELanguage, LanguageDict> language in landictToList)
                        {
                            colum += 1;
                            worksheet.Cells[row, colum].Value = language.Key.ToString();
                            worksheet.Column(colum).Width = 50;
                        }
                    }
                    else
                    {
                        worksheet.Cells[row, colum].Value = key;
                        foreach (KeyValuePair<ELanguage, LanguageDict> language in landictToList)
                        {
                            colum += 1;
                            LanguageDict dic = language.Value;
                            if (dic.book.ContainsKey(key))
                                worksheet.Cells[row, colum].Value = dic.book[key];
//                        else
//                            worksheet.Cells[row, colum].Value = "";
                        }
                    }

                    row += 1;
                }

                //保存excel
                using (FileStream fs = new FileStream(Excel, FileMode.OpenOrCreate, FileAccess.ReadWrite,
                    FileShare.ReadWrite))
                {
                    package.SaveAs(fs);
                }
            }
        }
    }
}